package compoundDB.database;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.junit.Test;

import compoundDB.core.Compound;
import compoundDB.database.container.ColumnField;
import compoundDB.database.container.SearchOption;
import compoundDB.database.enumeration.DataType;

/**
 * This class tests the DBManager implementation of the DatabaseAccessor interface.
 * 
 * @author Kohl Bromwich
 */
public class DBManagerTest
{
	@Test
	public void testOpenAndCloseDatabaseConnection() throws ClassNotFoundException,
			SQLException
	{
		DatabaseTests.setupTestDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		// Perform some database action that requires working connection
		List<ColumnField> cols = dbm.getCompoundTableColumns();
		
		assertTrue(cols.size() == 7);
		
		assertTrue(cols.get(0).getName().equals("guid"));
		assertTrue(cols.get(2).getName().equals("tags"));
		assertTrue(cols.get(6).getName().equals("notes"));
		
		dbm.closeDatabaseConnection();
		
		// Perform some database action that requires working connection
		try { 
			dbm.getCompoundTableColumns();
			fail("Database connection should be close");
		}catch(SQLException exc){}
	}
	
	@Test
	public void testAddGetRemoveRenameColumnFields() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		// Test Add some new columns
		dbm.addColumnField("newProp1", DataType.LONG);
		dbm.addColumnField("newProp2", DataType.BYTES);
		dbm.addColumnField("newProp3", DataType.DOUBLE);
		dbm.addColumnField("newProp4", DataType.FLOAT);
		dbm.addColumnField("newProp5", DataType.INTEGER);
		
		// Check exception is thrown on duplicate name
		try{
			dbm.addColumnField("newProp1", DataType.INTEGER);
			fail();
		}catch(SQLException exc){};
		
		// Test Get columns were added successfully
		List<ColumnField> cols = dbm.getCompoundTableColumns();
		
		assertTrue(cols.size() == 12);
		
		// Check pre-existing still exist
		assertTrue(cols.get(0).getName().equals("guid"));
		assertTrue(cols.get(1).getName().equals("ownerid"));
		assertTrue(cols.get(2).getName().equals("tags"));
		assertTrue(cols.get(3).getName().equals("smiles"));
		assertTrue(cols.get(4).getName().equals("structure"));
		assertTrue(cols.get(5).getName().equals("links"));
		assertTrue(cols.get(6).getName().equals("notes"));
		
		// Check new names
		assertTrue(cols.get(7).getName().equals("newProp1"));
		assertTrue(cols.get(8).getName().equals("newProp2"));
		assertTrue(cols.get(9).getName().equals("newProp3"));
		assertTrue(cols.get(10).getName().equals("newProp4"));
		assertTrue(cols.get(11).getName().equals("newProp5"));
		// Check new data types
		assertTrue(cols.get(7).getDataType().equals(DataType.LONG));
		assertTrue(cols.get(8).getDataType().equals(DataType.BYTES));
		assertTrue(cols.get(9).getDataType().equals(DataType.DOUBLE));
		assertTrue(cols.get(10).getDataType().equals(DataType.FLOAT));
		assertTrue(cols.get(11).getDataType().equals(DataType.INTEGER));
		// Check new column numbers
		assertTrue(cols.get(7).getColumnNumber() == 8);
		assertTrue(cols.get(8).getColumnNumber() == 9);
		assertTrue(cols.get(9).getColumnNumber() == 10);
		assertTrue(cols.get(10).getColumnNumber() == 11);
		assertTrue(cols.get(11).getColumnNumber() == 12);
		
		// Test Remove
		dbm.removeColumnField(cols.get(8));
		cols = dbm.getCompoundTableColumns();
		
		assertTrue(cols.size() == 11);
		// Check names are right
		assertTrue(cols.get(7).getName().equals("newProp1"));
		assertTrue(cols.get(8).getName().equals("newProp3"));
		assertTrue(cols.get(9).getName().equals("newProp4"));
		assertTrue(cols.get(10).getName().equals("newProp5"));
		// Check new data types
		assertTrue(cols.get(7).getDataType().equals(DataType.LONG));
		assertTrue(cols.get(8).getDataType().equals(DataType.DOUBLE));
		assertTrue(cols.get(9).getDataType().equals(DataType.FLOAT));
		assertTrue(cols.get(10).getDataType().equals(DataType.INTEGER));
		// Check new column numbers
		assertTrue(cols.get(7).getColumnNumber() == 8);
		assertTrue(cols.get(8).getColumnNumber() == 9);
		assertTrue(cols.get(9).getColumnNumber() == 10);
		assertTrue(cols.get(10).getColumnNumber() == 11);
		
		// Test Remove fails on initial columns.
		for (int x = 0; x < DBConfig.getNumberOfDefaultColumns(); x++)
		{
			try {
				dbm.removeColumnField(cols.get(x));
				fail("Should throw an exception removing default column");
			}catch(IllegalArgumentException exc){}
		}
		
		
		// Test Rename
		dbm.renameColumnField(cols.get(8), "renamedProp1");
		cols = dbm.getCompoundTableColumns();

		assertTrue(cols.size() == 11);
		// Check names are right
		assertTrue(cols.get(7).getName().equals("newProp1"));
		assertTrue(cols.get(8).getName().equals("renamedProp1"));
		assertTrue(cols.get(9).getName().equals("newProp4"));
		assertTrue(cols.get(10).getName().equals("newProp5"));
		// Check new data types
		assertTrue(cols.get(7).getDataType().equals(DataType.LONG));
		assertTrue(cols.get(8).getDataType().equals(DataType.DOUBLE));
		assertTrue(cols.get(9).getDataType().equals(DataType.FLOAT));
		assertTrue(cols.get(10).getDataType().equals(DataType.INTEGER));
		// Check new column numbers
		assertTrue(cols.get(7).getColumnNumber() == 8);
		assertTrue(cols.get(8).getColumnNumber() == 9);
		assertTrue(cols.get(9).getColumnNumber() == 10);
		assertTrue(cols.get(10).getColumnNumber() == 11);
		
		// Test Remove fails on initial columns.
		for (int x = 0; x < DBConfig.getNumberOfDefaultColumns(); x++)
		{
			try {
				dbm.renameColumnField(cols.get(x), "blah");
				fail("Should throw an exception renaming default column");
			}catch(IllegalArgumentException exc){}
		}
		
		
		dbm.closeDatabaseConnection();
	}
	
	@Test
	public void testDeleteCompound() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<Long> guids = new ArrayList<Long>();
		guids.add(4L);
		guids.add(1L);
		guids.add(3L);
		guids.add(2L);
		guids.add(5L);
		
		List<Long> deleteGuids = new ArrayList<Long>();
		deleteGuids.add(3L);
		deleteGuids.add(5L);
		dbm.deleteCompound(deleteGuids);
		
		QueryReader rdr = dbm.getCompounds(guids);
		
		assertTrue(rdr.getCompoundCount() == 3);
		
		List<Compound> comps = rdr.getCompounds(1, 3);

		assertTrue(comps.get(0).getGUID() == 1);
		assertTrue(comps.get(0).getNames().size() == 1);
		assertTrue(comps.get(0).getNames().get(0).equals("Test Compound"));
		assertTrue(comps.get(0).getOwnerID() == 0);
		assertTrue(comps.get(0).getProperties().size() == 7);
		assertTrue(comps.get(0).getProperty("tags").equals("test"));
		
		assertTrue(comps.get(1).getGUID() == 2);
		assertTrue(comps.get(1).getNames().size() == 2);
		assertTrue(comps.get(1).getNames().get(0).equals("comp21"));
		assertTrue(comps.get(1).getNames().get(1).equals("comp22"));
		assertTrue(comps.get(1).getOwnerID() == 2);
		assertTrue(comps.get(1).getProperties().size() == 7);
		assertTrue(comps.get(1).getProperty("tags").equals("tags2"));
		
		assertTrue(comps.get(2).getGUID() == 4);
		assertTrue(comps.get(2).getNames().size() == 3);
		assertTrue(comps.get(2).getNames().get(0).equals("comp41"));
		assertTrue(comps.get(2).getNames().get(1).equals("comp42"));
		assertTrue(comps.get(2).getNames().get(2).equals("comp43"));
		assertTrue(comps.get(2).getOwnerID() == 4);
		assertTrue(comps.get(2).getProperties().size() == 7);
		assertTrue(comps.get(2).getProperty("tags").equals("tags4"));
	}
	
	@Test
	public void testAddCompounds() throws SQLException, ClassNotFoundException
	{
		DatabaseTests.setupTestDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<Compound> compounds = new ArrayList<Compound>();
		
		Compound comp1 = new Compound(0, 0);
		List<String> names1 = new ArrayList<String>();
		names1.add("name11");
		names1.add("name12");
		names1.add("name13");
		comp1.setNames(names1);
		comp1.setProperty("ownerid", 1L);
		comp1.setProperty("tags", "tags test1");
		comp1.setProperty("smiles", "Test Smiles 1");
		comp1.setProperty("links", "test link 1");
		comp1.setProperty("notes", "test note 1");
		compounds.add(comp1);
		
		Compound comp2 = new Compound(0, 0);
		List<String> names2 = new ArrayList<String>();
		names2.add("name21");
		names2.add("name22");
		names2.add("name23");
		comp2.setNames(names2);
		comp2.setProperty("ownerid", 2L);
		//comp2.setProperty("tags", "tags test2"); // Test adding a compound without all fields (tags)
		comp2.setProperty("smiles", "Test Smiles 2");
		byte[] byt = new byte[8];
		Arrays.fill(byt, 0, 4, (byte)0);
		Arrays.fill(byt, 4, 8, (byte)5);
		comp2.setProperty("structure", byt);
		comp2.setProperty("links", "test link 2");
		comp2.setProperty("notes", "test note 2");
		compounds.add(comp2);
		
		Compound comp3 = new Compound(0, 0);
		List<String> names3 = new ArrayList<String>();
		names3.add("name11"); // Should allow duplicate names
		names3.add("name32");
		names3.add("name33");
		comp3.setNames(names3);
		comp3.setProperty("ownerid", 3L);
		comp3.setProperty("tags", "tags test3");
		comp3.setProperty("smiles", "Test Smiles 3");
		//comp3.setProperty("links", "test link 3"); // Test adding a compound without all fields (links)
		comp3.setProperty("notes", "test note 3");
		compounds.add(comp3);
		
		dbm.addCompounds(compounds);
		
		// Check database directly
		Connection conn = DatabaseTests.getNewConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM compounds");// ORDER BY guid ASC;");
		
		rs.next();
		
		rs.next();
		assertTrue(rs.getInt(1) == 2);
		assertTrue(rs.getInt(2) == 1);
		assertTrue(rs.getString(3).equals("tags test1"));
		assertTrue(rs.getString(4).equals("Test Smiles 1"));
		assertTrue(rs.getString(6).equals("test link 1"));
		assertTrue(rs.getString(7).equals("test note 1"));
		
		rs.next();
		assertTrue(rs.getInt(1) == 3);
		assertTrue(rs.getInt(2) == 2);
		assertTrue(rs.getString(3) == null);
		assertTrue(rs.getString(4).equals("Test Smiles 2"));
		assertTrue(Arrays.equals(rs.getBytes(5), byt));
		assertTrue(rs.getString(6).equals("test link 2"));
		assertTrue(rs.getString(7).equals("test note 2"));
		
		rs.next();
		assertTrue(rs.getInt(1) == 4);
		assertTrue(rs.getInt(2) == 3);
		assertTrue(rs.getString(3).equals("tags test3"));
		assertTrue(rs.getString(4).equals("Test Smiles 3"));
		assertTrue(rs.getString(6) == null);
		assertTrue(rs.getString(7).equals("test note 3"));
		
		rs.close();
		rs = st.executeQuery("SELECT * FROM compound_names");
		
		rs.next();
		
		rs.next();
		assertTrue(rs.getString(2).equals("name11"));
		rs.next();
		assertTrue(rs.getString(2).equals("name12"));
		rs.next();
		assertTrue(rs.getString(2).equals("name13"));
		rs.next();
		assertTrue(rs.getString(2).equals("name21"));
		rs.next();
		assertTrue(rs.getString(2).equals("name22"));
		rs.next();
		assertTrue(rs.getString(2).equals("name23"));
		rs.next();
		assertTrue(rs.getString(2).equals("name11"));
		rs.next();
		assertTrue(rs.getString(2).equals("name32"));
		rs.next();
		assertTrue(rs.getString(2).equals("name33"));
		
		rs.close();
		st.close();
		conn.close();
	}
	
	
	
	@Test
	public void testAutoCompleteName() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<String> names;
		
		names = dbm.autoCompleteName("comp", 1);
		assertTrue(names.get(0).equals("comp21"));

		names = dbm.autoCompleteName("comp4", 20);
		assertTrue(names.get(0).equals("comp41"));
		assertTrue(names.get(1).equals("comp42"));
		assertTrue(names.get(2).equals("comp43"));
		try{names.get(3);
			fail("Should not be a 4th name returned");
		}catch(IndexOutOfBoundsException exc){}

		names = dbm.autoCompleteName("c", 0);
		try{names.get(0);
			fail("Should not be a name returned");
		}catch(IndexOutOfBoundsException exc){}
	}
	
	@Test
	public void testGetCompoundsGuids() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<Long> guids = new ArrayList<Long>();
		guids.add(4l);
		guids.add(1l);
		guids.add(2l);
		
		QueryReader rdr = dbm.getCompounds(guids);
		
		assertTrue(rdr.getCompoundCount() == 3);
		
		List<Compound> comps = rdr.getCompounds(1, 3);

		assertTrue(comps.get(0).getGUID() == 1);
		assertTrue(comps.get(0).getNames().size() == 1);
		assertTrue(comps.get(0).getNames().get(0).equals("Test Compound"));
		assertTrue(comps.get(0).getOwnerID() == 0);
		assertTrue(comps.get(0).getProperties().size() == 7);
		assertTrue(comps.get(0).getProperty("tags").equals("test"));
		
		assertTrue(comps.get(1).getGUID() == 2);
		assertTrue(comps.get(1).getNames().size() == 2);
		assertTrue(comps.get(1).getNames().get(0).equals("comp21"));
		assertTrue(comps.get(1).getNames().get(1).equals("comp22"));
		assertTrue(comps.get(1).getOwnerID() == 2);
		assertTrue(comps.get(1).getProperties().size() == 7);
		assertTrue(comps.get(1).getProperty("tags").equals("tags2"));
		
		assertTrue(comps.get(2).getGUID() == 4);
		assertTrue(comps.get(2).getNames().size() == 3);
		assertTrue(comps.get(2).getNames().get(0).equals("comp41"));
		assertTrue(comps.get(2).getNames().get(1).equals("comp42"));
		assertTrue(comps.get(2).getNames().get(2).equals("comp43"));
		assertTrue(comps.get(2).getOwnerID() == 4);
		assertTrue(comps.get(2).getProperties().size() == 7);
		assertTrue(comps.get(2).getProperty("tags").equals("tags4"));
	}
	
	@Test
	public void testGetCompoundsName() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
				
		QueryReader rdr = dbm.getCompounds("comp22");
		
		assertTrue(rdr.getCompoundCount() == 1);
		
		List<Compound> comps = rdr.getCompounds(1, 3);

		assertTrue(comps.get(0).getGUID() == 2);
		assertTrue(comps.get(0).getNames().size() == 2);
		assertTrue(comps.get(0).getNames().get(0).equals("comp21"));
		assertTrue(comps.get(0).getNames().get(1).equals("comp22"));
		assertTrue(comps.get(0).getOwnerID() == 2);
		assertTrue(comps.get(0).getProperties().size() == 7);
		assertTrue(comps.get(0).getProperty("tags").equals("tags2"));
	}
	
	@Test
	public void testGetNames() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<String> names;
		
		names = dbm.getNames(1);
		assertTrue(names.size() == 1);
		assertTrue(names.get(0).equals("Test Compound"));
		
		names = dbm.getNames(4);
		assertTrue(names.size() == 3);
		assertTrue(names.get(0).equals("comp41"));
		assertTrue(names.get(1).equals("comp42"));
		assertTrue(names.get(2).equals("comp43"));
		
		names = dbm.getNames(23);
		assertTrue(names.size() == 0);
	}
	
	@Test
	public void testSearchCompounds() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		List<ColumnField> cols = dbm.getCompoundTableColumns();
		
		// Setup search filters
		SearchOption so1 = SearchOption.createSearchContains(cols.get(2), "gs");
		SearchOption so2 = SearchOption.createSearchRange(cols.get(0), 2, 4);
		
		List<SearchOption> sos = new ArrayList<SearchOption>();
		sos.add(so1);
		sos.add(so2);
		
		// Perform search
		QueryReader rdr = dbm.searchCompounds(sos);
		assertTrue(rdr.getCompoundCount() == 3);
		
		// Check results
		List<Compound> comps = rdr.getCompounds(1, 25);
		assertTrue(comps.size() == 3);
		
		assertTrue(comps.get(0).getGUID() == 2);
		assertTrue(comps.get(0).getOwnerID() == 2);
		assertTrue(comps.get(0).getNames().size() == 2);
		assertTrue(comps.get(0).getNames().get(0).equals("comp21"));
		assertTrue(comps.get(0).getNames().get(1).equals("comp22"));
		assertTrue(comps.get(0).getProperty("tags").equals("tags2"));

		assertTrue(comps.get(1).getGUID() == 3);
		assertTrue(comps.get(1).getOwnerID() == 3);
		assertTrue(comps.get(1).getNames().size() == 1);
		assertTrue(comps.get(1).getNames().get(0).equals("comp31"));
		assertTrue(comps.get(1).getProperty("tags").equals("tags3"));

		assertTrue(comps.get(2).getGUID() == 4);
		assertTrue(comps.get(2).getOwnerID() == 4);
		assertTrue(comps.get(2).getNames().size() == 3);
		assertTrue(comps.get(2).getNames().get(0).equals("comp41"));
		assertTrue(comps.get(2).getNames().get(1).equals("comp42"));
		assertTrue(comps.get(2).getProperty("tags").equals("tags4"));
		
		// Check fails on no search filters
		sos.clear();
		try{
			dbm.searchCompounds(sos);
			fail("Should not search with no filters");
		}catch(IllegalArgumentException exc){}
		
	}
	
	@Test
	public void testUpdateCompound() throws ClassNotFoundException, SQLException
	{
		DatabaseTests.setupTestDatabase();
		DatabaseTests.populateDatabase();
		DBManager dbm = new DBManager();
		dbm.openDatabaseConnection();
		
		// Get compound
		List<Long> guids = new ArrayList<Long>();
		guids.add(2L);
		
		QueryReader rdr = dbm.getCompounds(guids);
		Compound comp = rdr.getCompounds(1, 1).get(0);
		
		// Edit compound
		comp.setProperty("tags", "edited tag");
		List<String> names = comp.getNames();
		names.clear();
		names.add("edited name");
		names.add("edited name again");
		comp.setNames(names);
		comp.setProperty("links", "www.xkcd.com/505/");
		
		// Submit changes
		dbm.updateCompound(comp);
		
		// Get updated compound
		rdr = dbm.getCompounds(guids);
		comp = rdr.getCompounds(1, 1).get(0);
		
		// Check changes were made correctly
		assertTrue(comp.getNames().size() == 2);
		assertTrue(comp.getNames().get(0).equals("edited name"));
		assertTrue(comp.getNames().get(1).equals("edited name again"));
		assertTrue(comp.getProperty("tags").equals("edited tag"));
		assertTrue(comp.getProperty("links").equals("www.xkcd.com/505/"));
	}
}
